package com.yan.advenced;

import com.yan.advenced.pojo.Employee;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCAdvanced {
    @Test
    public void testORM() throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name,emp_salary, emp_age FROM t_emp where emp_id = ?");
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();
        Employee employee = null;
        if (resultSet.next()) {
            employee = new Employee();
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            double salary = resultSet.getDouble(3);
            int age = resultSet.getInt(4);
            //为对象的属性赋值:
            employee.setId(id);
            employee.setName(name);
            employee.setSalary(salary);
            employee.setAge(age);
        }
        System.out.println(employee);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testORMList() throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name,emp_salary, emp_age FROM t_emp");
        ResultSet resultSet = preparedStatement.executeQuery();
        Employee employee = null;
        List<Employee> employees = new ArrayList<>();
        while (resultSet.next()) {
            employee = new Employee();

            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            double salary = resultSet.getDouble(3);
            int age = resultSet.getInt(4);

            //为对象的属性赋值:
            employee.setId(id);
            employee.setName(name);
            employee.setSalary(salary);
            employee.setAge(age);

            employees.add(employee);
        }
        for (Employee e : employees) {
            System.out.println(e);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testReturnPK() throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");

        String sql = "INSERT INTO t_emp(emp_name,emp_salary,emp_age) VALUES(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        Employee employee = new Employee(null, "jack", 123.45, 29);
        preparedStatement.setString(1, employee.getName());
        preparedStatement.setDouble(2, employee.getSalary());
        preparedStatement.setInt(3, employee.getAge());

        int result = preparedStatement.executeUpdate();
        ResultSet resultSet = null;

        if (result > 0) {
            System.out.println("插入成功");
            //获取插入数据的id:
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()) {
                int id = resultSet.getInt(1);
                employee.setId(id);
            }
            System.out.println(employee);
        }
        else {
            System.out.println("插入失败");
        }

        if (resultSet != null) {
            resultSet.close();
        }
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testMoreInsert()throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");

        String sql = "INSERT INTO t_emp(emp_name, emp_salary, emp_age) VALUES(?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //获取执行时间
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {

            preparedStatement.setString(1, "marry" + i);
            preparedStatement.setDouble(2, 1000 + i);
            preparedStatement.setInt(3, 20 + i);

            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("执行时间：" + (end - start));

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testBatch()throws Exception{
        Connection connection = DriverManager.getConnection("jdbc:mysql:///stguigu?rewriteBatchedStatements=true", "root", "123456");

        /*
        注意一：必须在连接数据库的URL后面追加rewriteBatchedStatements=true
        注意二：新增sql是必须使用values，且语句后面不追加；
        注意三：调用addBatch方法时.
        注意四：调用executeBatch方法时.
        注意五：调用clearBatch方法时.
         */
        String sql = "INSERT INTO t_emp(emp_name, emp_salary, emp_age) VALUES(?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //获取执行时间
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {

            preparedStatement.setString(1, "marry" + i);
            preparedStatement.setDouble(2, 1000 + i);
            preparedStatement.setInt(3, 20 + i);

            preparedStatement.addBatch();
        }
        //执行批量操作
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("执行时间：" + (end - start));

        preparedStatement.close();
        connection.close();
    }
}
